package com.DAO;

import com.Grade;
import com.JdbcUtils;
import com.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GradeDao {

    // 添加成绩
    public void addGrade(Grade grade) throws SQLException {
        String sql = "INSERT INTO grades (student_id, course_name, grade) VALUES (?, ?, ?)";
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, grade.getStudentId());
            stmt.setString(2, grade.getCourseName());
            stmt.setDouble(3, grade.getGrade());
            stmt.executeUpdate();
        }
    }

    // 更新成绩
    public void updateGrade(Grade grade) throws SQLException {
        String sql = "UPDATE grades SET course_name = ?, grade = ? WHERE student_id = ? AND id = ?";
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, grade.getCourseName());
            stmt.setDouble(2, grade.getGrade());
            stmt.setInt(3, grade.getStudentId());
            stmt.setInt(4, grade.getId());
            stmt.executeUpdate();
        }
    }

    // 删除成绩
    public void deleteGrade(int id) throws SQLException {
        String sql = "DELETE FROM grades WHERE id = ?";
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, id);
            stmt.executeUpdate();
        }
    }

    // 根据学生ID查询成绩
    public List<Grade> getGradesByStudentId(int studentId) throws SQLException {
        List<Grade> grades = new ArrayList<>();
        String sql = "SELECT * FROM grades WHERE student_id = ?";
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, studentId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Grade grade = new Grade();
                grade.setId(rs.getInt("id"));
                grade.setStudentId(rs.getInt("student_id"));
                grade.setCourseName(rs.getString("course_name"));
                grade.setGrade(String.valueOf(rs.getDouble("grade")));
                grades.add(grade);
            }
        }
        return grades;
    }

    // 获取所有成绩
    public List<Grade> getAllGrades() throws SQLException {
        List<Grade> grades = new ArrayList<>();
        String sql = "SELECT * FROM grades";
        try (Connection conn = JdbcUtils.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                Grade grade = new Grade();
                grade.setId(rs.getInt("id"));
                grade.setStudentId(rs.getInt("student_id"));
                grade.setCourseName(rs.getString("course_name"));
                grade.setGrade(String.valueOf(rs.getDouble("grade")));
                // 通过 studentId 查询对应的学生姓名
                StudentDao studentDao = new StudentDao();
                Student student = studentDao.getStudentByStudentId(String.valueOf(rs.getInt("student_id")));
                grade.setStudentName(student != null ? student.getName() : "未知");
                grades.add(grade);
            }
        }
        return grades;
    }

    // 获取指定学生的成绩
    public Grade getGradeById(int id) throws SQLException {
        Grade grade = null;
        String sql = "SELECT * FROM grades WHERE id = ?";
        try (Connection conn = JdbcUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                grade = new Grade();
                grade.setId(rs.getInt("id"));
                grade.setStudentId(rs.getInt("student_id"));
                grade.setCourseName(rs.getString("course_name"));
                grade.setGrade(String.valueOf(rs.getDouble("grade")));
            }
        }
        return grade;
    }
}
